package com.xuan.task.Mapper;

import com.xuan.task.domain.WordAndCount;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface WordPracticeRecordsMapper {


    /**
     * 获取单词档案和单词训练中所有的用户
     * @return
     */
    @Select( "select DISTINCT user_id from (" +
            "      select DISTINCT user_id from word_practice_file where create_time > #{startTime} and create_time < #{endTime}" +
            "      union" +
            "      select DISTINCT user_id from word_practice_records where create_time > #{startTime} and create_time  < #{endTime}" +
            "      ) tt" )
    List<String> getAllUserIdFromWordFileAndWordRecords(@Param( "startTime" ) String startTime, @Param( "endTime" )String endTime);

    @Select( "select DISTINCT word  from (" +
            "            select word from word_practice_file where user_id=#{userId}" +
            "            and word in (select word from resource_word_relation" +
            "            where vocabulary_resource_num in (select resource_library_num  from  electronic_library_relation where book_num =#{bookNum}))" +
            "            union" +
            "            select DISTINCT  word from word_practice_records_new where user_id=#{userId}" +
            "            and unit_name in (select resource_library_num  from  electronic_library_relation where book_num =#{bookNum}) and word !='' ) tt" +
            "            where word in (SELECT DISTINCT word FROM resource_word_relation r" +
            "                  join vocabulary_resource_library vr on r.vocabulary_resource_num = vr.resource_library_num" +
            "                  WHERE vocabulary_resource_num in (select resource_library_num  from  electronic_library_relation where book_num =#{bookNum})" +
            "                  and word in (select word from vocabulary where audio_path!='' and is_delete=0 ))" )
    List<String> getUserWordListByBookNumAndUserId(@Param( "userId" ) String userId, @Param( "bookNum" )String bookNum);

    //根据电子书和用户ID，获取用户单词分数大于80的单词和次数 表：word_practice_records 改为：word_practice_records_new
    @Select( " select  word,count(*) wordCount from word_practice_records_new where user_id=#{userId} and score >=80" +
            "            and unit_name in (select resource_library_num  from  electronic_library_relation where book_num =#{bookNum})" +
            "            and word in (SELECT DISTINCT word FROM resource_word_relation r" +
            "                  join vocabulary_resource_library vr on r.vocabulary_resource_num = vr.resource_library_num" +
            "                  WHERE vocabulary_resource_num in (select resource_library_num  from  electronic_library_relation where book_num =#{bookNum})" +
            "                  and word in (select word from vocabulary where audio_path!='' and is_delete=0 ))" +
            "            group by word" )
    List<WordAndCount> getWordAndCountByBookNumAndUserId(@Param( "userId" ) String userId, @Param( "bookNum" ) String bookNum);



}
